package quanlithpt;

/*
 * @author codercui
 */
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.table.DefaultTableModel;

public class SQL {

    private Connection connect;
    Statement sta;
    ResultSet ketqua;
    ResultSetMetaData tt_kq;
    /*########################^^^^^^^ SQL cơ bản ^^^^^^^########################*/
    /*###########################################################################*/
    public Connection getConnect() throws SQLException{
        String url = "jdbc:mysql://localhost:3306/thpt";   // chỉnh font, 
        connect = DriverManager.getConnection(url, "root", "admin@123");
        return this.connect;
    }
    public void connectDB() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/thpt?setUnicode=true&characterEncoding=UTF-8";   // chỉnh font, 
            connect = DriverManager.getConnection(url, "root", "admin@123");
        } catch (SQLException | ClassNotFoundException ex) {
            JOptionPane.showMessageDialog(null, "Không kết nối CSDL được");
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    /*###########################################################################*/

    public void closeDB() {
        try {
            connect.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    /*###########################################################################*/

    public String select(String cot, String bang, String dk /*có WHERE*/) {
        connectDB();
        String trave = "";
        try {
            sta = (Statement) connect.createStatement();
            //   String url="SELECT "+cot+" FROM "+bang+" "+dk;
            //   JOptionPane.showMessageDialog(null, url);
            ketqua = sta.executeQuery("SELECT " + cot + " FROM " + bang + " " + dk);
            while (ketqua.next()) {
                trave = ketqua.getString(1);
            }
            ketqua.close();
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "SELECT không thành công");
        }
        closeDB();// có try catch trong hàm rồi
        return (trave);
    }
    /*###########################################################################*/

    public int selectInt(String cot, String bang, String dk /*có WHERE*/) {
        connectDB();
        int trave = 0;
        try {
            sta = (Statement) connect.createStatement();
            ketqua = sta.executeQuery("SELECT " + cot + " FROM " + bang + dk);
            while (ketqua.next()) {
                trave = ketqua.getInt(1);
            }
            ketqua.close();
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "SELECT không thành công");
        }
        closeDB();// có try catch trong hàm rồi
        return trave;
    }
    /*###########################################################################*/

    public int insert(String cac_cot, String bang, String cac_gt) {
        connectDB();
        int executeUpdate;
        try {
            sta = (Statement) connect.createStatement();
            executeUpdate = sta.executeUpdate("INSERT INTO " + bang + " (" + cac_cot + ") values (" + cac_gt + ")");
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "INSERT không thành công");
            executeUpdate = 0;
        }
        closeDB();// có try catch trong hàm rồi
        return (executeUpdate);
    }
    /*###########################################################################*/
//hàm tìm kiếm

    public String search(JTextArea txa, String noidung, int doituong, int loaitt) throws SQLException {
        String trave = "";
        int vitri = noidung.lastIndexOf(" ");
        connectDB();
        sta = (Statement) connect.createStatement();
        if (doituong == 0) { //tìm giáo viên
            if (loaitt == 0) {
                String holot = noidung.substring(0, vitri);
                String ten = noidung.substring(vitri + 1, noidung.length());
                ketqua = sta.executeQuery("select * from giao_vien where Ho_GV='" + holot + "' and Ten_GV='" + ten + "'");
            } else if (loaitt == 1) {
                ketqua = sta.executeQuery("select * from giao_vien where MS_GV= " + noidung);
            }
        } else {  // tìm học sinh
            if (loaitt == 0) {
                String holot = noidung.substring(0, vitri);
                String ten = noidung.substring(vitri + 1, noidung.length());
                ketqua = sta.executeQuery("select * from hoc_sinh where Ho_HS='" + holot + "' and Ten_HS='" + ten + "'");
            } else if (loaitt == 1) {
                ketqua = sta.executeQuery("select * from hoc_sinh where MS_HS= " + noidung);
            }
        }
        while (ketqua.next()) {
            if (doituong == 0) {
                trave = "Họ Tên: " + ketqua.getString(2) + " " + ketqua.getString(3) + "\nNăm Sinh: " + ketqua.getString(5)
                        + "\nĐịa Chỉ: " + ketqua.getString(6) + "\nNăm vào trường: " + ketqua.getString(7)
                        + "\nChức Vụ: " + ketqua.getString(8) + "\nTrình Độ: " + ketqua.getString(9)
                        + "\nBộ môn: " + ketqua.getString(10) + "\n______________________________________________________________\n";
                txa.append(trave);
            } else {
                trave = "Họ Tên: " + ketqua.getString(2) + " " + ketqua.getString(3) + "\nNăm sinh: " + ketqua.getString(4)
                        + "\nĐịa Chỉ: " + ketqua.getString(6) + "\nLớp: " + ketqua.getString(7)
                        + "\nHọc phí: " + ketqua.getString(8) + "\nSố ngày vắng: " + ketqua.getString(9)
                        + "\n______________________________________________________________\n";
                txa.append(trave);
            }
        }
        closeDB();
        return trave;
    }
    /*###########################################################################*/
//hàm thống kê

    public void statistic(String bo_mon, String do_tuoi, String gioi_tinh, String tham_nien, JTable display) {

        connectDB();
        Vector hang;
        DefaultTableModel model;
        Vector tieu_de = new Vector();
        tieu_de.add("Tiêu chí thống kê");
        tieu_de.add("Giá trị thống kê");
        tieu_de.add("Số lượng");
        model = new DefaultTableModel(tieu_de, 0) {
            public boolean isCellEditable(int rowIndex, int mColIndex) {
                return false;
            }
        }; // không cho edit trên table      
        String dk = "";
        try {
            sta = (Statement) connect.createStatement();
            /// thống kê theo bộ môn
            if (!bo_mon.equals(" ")) {
                ketqua = sta.executeQuery("Select count(ms_gv) as so_luong from giao_vien where bo_mon = '" + bo_mon + "'");                
                while (ketqua.next()) { 
                    hang = new Vector();
                    hang.add("Bộ Môn"); hang.add(bo_mon); hang.add(ketqua.getInt(1));
                    model.addRow(hang);
                }                
            }
            /// thống kê theo độ tuổi
            if (!do_tuoi.equals("")) {
                switch (do_tuoi) {
                        case "< 30":
                            dk = "(year(curdate()) - year(nam_sinh)) < 30";
                            break;
                        case "30 - 50":
                            dk = "(year(curdate()) - year(nam_sinh)) >= 30 and (year(curdate()) - year(nam_sinh)) <= 50";
                            break;
                        case "> 50":
                            dk = "(year(curdate()) - year(nam_sinh)) > 50";
                            break;
                        default:
                            dk = " 1 = 0";
                            break;
                    }
                ketqua = sta.executeQuery("Select count(ms_gv) as so_luong from giao_vien where "+ dk);                
                while (ketqua.next()) { 
                    hang = new Vector();
                    hang.add("Độ tuổi"); hang.add(do_tuoi); hang.add(ketqua.getInt(1));
                    model.addRow(hang);
                }                
            }
            // thống kê theo giới tính
            if (!gioi_tinh.equals("")) {
                switch (gioi_tinh) {
                        case "Nam":
                            dk = " gioi_tinh = 1";
                            break;
                        case "Nữ":
                            dk = " gioi_tinh = 0";
                            break;
                        default:
                            dk = " 1 = 0";
                            break;
                    }
                ketqua = sta.executeQuery("Select count(ms_gv) as so_luong from giao_vien where "+ dk);                
                while (ketqua.next()) { 
                    hang = new Vector();
                    hang.add("Giới tính"); hang.add(gioi_tinh); hang.add(ketqua.getInt(1));
                    model.addRow(hang);
                }                
            }
            /// thống kê theo thâm niên
            if (!tham_nien.equals("")) {
                switch (tham_nien) {
                        case "< 5 năm":
                            dk = "(year(curdate()) - year(nam_vao_truong)) < 30";
                            break;
                        case "5 - 10 năm":
                            dk = "(year(curdate()) - year(nam_vao_truong)) >= 30 and (year(curdate()) - year(nam_sinh)) <= 50";
                            break;
                        case "> 10 năm":
                            dk = "(year(curdate()) - year(nam_vao_truong)) > 50";
                            break;
                        default:
                            dk = " 1 = 0";
                            break;
                    }
                ketqua = sta.executeQuery("Select count(ms_gv) as so_luong from giao_vien where "+ dk);                
                while (ketqua.next()) { 
                    hang = new Vector();
                    hang.add("Thâm niên"); hang.add(tham_nien); hang.add(ketqua.getInt(1));
                    model.addRow(hang);
                }                
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        display.setModel(model);
        closeDB();
    }
    /*###########################################################################*/
//hàm hiển thị combobox

    public void load(String loai, JComboBox display, int truonghop) throws SQLException {
        String kt = "";
        display.addItem("");
        if (truonghop == 0) {
            connectDB();
            sta = (Statement) connect.createStatement();
            System.out.print("select " + loai + " from giao_vien");
            ketqua = sta.executeQuery("select " + loai + " from giao_vien");
            while (ketqua.next()) {

                if (kt.indexOf(ketqua.getString(1)) < 0) {
                    display.addItem(ketqua.getString(1));
                }
                kt += "+" + ketqua.getString(1);
            }
        }
    }

    public int update(String bang, String nd, String dk/*có WHERE*/) {
        connectDB();
        int executeUpdate;
        try {
            sta = (Statement) connect.createStatement();
            executeUpdate = sta.executeUpdate("UPDATE " + bang + " SET " + nd + dk);
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "UPDATE không thành công");
            executeUpdate = 0;
        }
        closeDB();// có try catch trong hàm rồi
        return (executeUpdate);
    }
    /*###########################################################################*/

    public int delete(String bang, String dk) {
        connectDB();
        int executeUpdate;
        try {
            sta = (Statement) connect.createStatement();
            System.out.println(("DELETE FROM " + bang + " " + dk));
            executeUpdate = sta.executeUpdate("DELETE FROM " + bang + " " + dk);
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "DELETE không thành công");
            executeUpdate = 0;
        }
        closeDB();// có try catch trong hàm rồi
        return (executeUpdate);
    }
    /*###########################################################################*/

    public void show() {
        try {
            connectDB();
            sta = (Statement) connect.createStatement();
            ketqua = sta.executeQuery("SELECT * from hoc_sinh");
            tt_kq = (ResultSetMetaData) ketqua.getMetaData();
            int cot = tt_kq.getColumnCount();
            for (int i = 1; i <= cot; i++) {
                System.out.print(tt_kq.getColumnLabel(i) + " | ");
            }
            while (ketqua.next()) {
                for (int j = 1; j <= cot; j++) {
                    System.out.print(ketqua.getString(j) + " | ");
                }
                System.out.println();
            }
            ketqua.close();
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeDB();
    }
    /*########################^^^^^^^ Ứng dụng SQL ^^^^^^^########################*/
    /*###########################################################################*/

    public int dang_nhap(String user, String pass) {
        if (select("ten_dn", "tai_khoan", " Where ten_dn='" + user + "' and " + "pass='" + pass + "'").equals(user)) {
            if (select("mo_tat", "tai_khoan", "Where ten_dn='" + user + "'").equals("1")) {
                int loai = Integer.parseInt(select("loai_tk", "tai_khoan", "where ten_dn='" + user + "'"));
                return (1 + loai); // thành công
            } else {
                return (0);   // tài khoản bị khóa
            }
        } else {
            return (-1); // không thành công
        }
    }
    /*###########################################################################*/
    // có trả về >0, không có thì trả về 0

    public int check_tk(String tk) {
        String tendn = select("ten_dn", "tai_khoan", " where ten_dn= '" + tk + "'");
        if (tendn.equals("")) {
            return 0;
        }
        return 1;
    }

    public void fill_combo(JComboBox a, String cot, String bang) {
        try {
            a.removeAllItems(); // xóa hết cái cũ nếu không nó lại cộng thêm
            connectDB();
            sta = (Statement) connect.createStatement();
            ketqua = sta.executeQuery("SELECT " + cot + " from " + bang);
            while (ketqua.next()) {
                a.addItem(ketqua.getString(1));
            }
            ketqua.close();
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeDB();
    }

    public void fill_combo(JComboBox a, String cot, String bang, boolean distinct) {
        connectDB();
        if (distinct) {
            try {
                a.removeAllItems();
                a.addItem(" ");
                sta = (Statement) connect.createStatement();
                ketqua = sta.executeQuery("SELECT distinct " + cot + " from " + bang);
                while (ketqua.next()) {
                    a.addItem(ketqua.getString(1));
                }
                ketqua.close();
                sta.close();
            } catch (SQLException ex) {
                Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            fill_combo(a, cot, bang);
        }
        closeDB();
    }

    public void fill_combo(JComboBox a, String cot1, String cot2, String bang) {
        try {
            a.removeAllItems();
            connectDB();
            sta = (Statement) connect.createStatement();
            ketqua = sta.executeQuery("SELECT " + cot1 + "," + cot2 + " from " + bang);
            while (ketqua.next()) {
                a.addItem(ketqua.getString(1) + " " + ketqua.getString(2));
            }
            ketqua.close();
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeDB();
    }

    public void fill_combo(JComboBox a, String cot1, String cot2, String bang, String dk) {
        try {
            a.removeAllItems();
            connectDB();
            sta = (Statement) connect.createStatement();
            ketqua = sta.executeQuery("SELECT " + cot1 + "," + cot2 + " from " + bang + dk);
            while (ketqua.next()) {
                a.addItem(ketqua.getString(1) + " " + ketqua.getString(2));
            }
            ketqua.close();
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeDB();
    }

    public void fill_combo1(JComboBox a, String cot, String bang, String dk) {
        try {
            a.removeAllItems();
            connectDB();
            sta = (Statement) connect.createStatement();
            ketqua = sta.executeQuery("SELECT " + cot + " from " + bang + dk);
            while (ketqua.next()) {
                a.addItem(ketqua.getString(1));
            }
            ketqua.close();
            sta.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeDB();
    }

    /*

     public static void main(String[] args) {
     SQL a=new SQL();
     String cac_cot="ten_dn, pass, email, loai_tk, mo_tat";
     String gt= "'ádf', '123', 'ádasd', 1, 1";
     a.insert(cac_cot, "giao_vien", gt);
     if(a.check_tk("gvien111")>0){
     JOptionPane.showMessageDialog(null, "có rồi");
     }
     else JOptionPane.showMessageDialog(null, "chưa có");   */
    //     String b="2000-20-06";
    // System.out.println( a.xu_lyngay("2000-20-06"));
    //     System.out.println( b.substring(8,10));
    // System.out.print("hamburger".substring(4, 8));
}
